import pandas as pd
import pymysql

# 从数据库中读取数据
def read_data():
    # 连接数据库
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='food_price',
        cursorclass=pymysql.cursors.DictCursor  # 返回字典形式的结果
    )

    try:
        with connection.cursor() as cursor:
            # 执行查询语句
            sql = "SELECT * FROM merge_info"
            cursor.execute(sql)

            # 获取查询结果
            result = cursor.fetchall()
            # 将结果转换为DataFrame
            df = pd.DataFrame(result)
            return df
    finally:
        # 关闭数据库连接
        connection.close()


# 加载数据集
class Price_Data():
    def __init__(self):
        # 初始化数据
        self.df = read_data()

    def output(self):
        return self.df

    def max_count(self):
        counts = self.df['food_name'].value_counts()
        top_four = counts.head(4)
        top_four_dict = top_four.to_dict()
        return top_four_dict
